Summing and Grouping

This example demonstrates how to use the Summing and Grouping capabilities in the Argos DataBlock Designer.  This capability allows you to visually create SQL HAVING and GROUP BY statements.

This simple example will create a Dashboard in which sales results of employees will be presented.  Results will be grouped by employee; thus the SQL GROUP BY statement will be created.  The results will show only sales totals below a specified amount, with an SQL HAVING statement created.  Also, the SQL aggregate SUM function will be visually generated to create the sales totals.

The Employees, Orders, Order_Details, and Products tables within the sample database will be utilized.  These are the same tables that were used in Example 1.

New SQL Statements in this example: GROUP BY, HAVING, and SQL AGGREGATE functions.

Creating the Form

The input required from the user for this report will be a date range, and a sales total threshold that will be used to filter the query.

Create a form as shown below.  The variable names for the three input selection fields are StartDate, EndDate, and SalesTotal.  The SalesTotal variable will be used to specify a sales total that is used to display names of sales persons whose total are less than this amount.

This image shows the form containing start date, end date, and sales total fields for entering data and a multi-column list box for display of query results.

The query for the multi-column list box containing the query results are shown in the figure below.  Only two fields will be displayed in the Dashboard, the employee ID and a calculated field (total_sales) containing the sales total (quantity times unit price).

Data entered under the SELECT tab.

The WHERE clause below is used to find records within the date range specified by the person executing the report.

The data entered under the WHERE tab.

GROUP BY and SUM fields

To sum the sales records and group them by employee,

  1. Click the “Summing” icon summing icon which displays an additional row titled “Summing” as shown below.
  2. Under the Employees column, select <Group By>.
  3. Under the <calculated>, select Sum for the calculated field since this data is to be summed.  These selections are used to create the GROUP BY statement.

This image shows the data entered into the Summing field.

HAVING Tab

The HAVING tab in the figure below specifies a condition where sales total is less than the amount entered.  The SalesTotal variable contains the value entered by the user executing the report with that value compared to the calculated field above it.  If the SalesTotal is less than or equal to the input selection, that employee will be included in the report.  This data is used to create the SQL HAVING statement.
This image shows the data entered under the HAVING tab.

The final SQL:
This image shows the SQL that was created.

Note the existence of the GROUP BY and HAVING statements.


Creating complex WHERE and HAVING statements:

Note the box at the left portion of the window containing the SELECT,WHERE, HAVING, ORDER BY tabs.  By clicking <root> then clicking the plus sign, you can create nested WHERE or HAVING clauses, each enclosed in parentheses and containing its own set of conditionals. 
This image shows how nested WHERE or HAVING clauses are displayed.
The tree structure above would create SQL structured as follows:
WHERE [Join conditions]
     AND [conditions in #1 ]
             AND [conditions in #2]
             AND [conditions in #3] 
                    AND [conditions in #4]

 

 

If the Summing fields are no longer desired, make sure to click the Summing icon to remove the row with the Summing fields. Otherwise Argos will expect entries in the Summing fields.

Results

Executing the Dashboard report produces the following which lists sales employees with sales less than $1,000,000 between 1/3/2005 and 4/5/2010.
This image shows the QuickView report results.

Return to the  Charting with multiple series example